package com.ch.base.action;

import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;

import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;

import com.ch.base.dao.GenerateCodeDAO;
import com.ch.base.dao.GenerateCodeWrapper;
import com.ch.base.element.BaseAction;
import com.ch.base.obj.QueryResultObj;
import com.ch.base.obj.QuerySettingObj;
import com.ch.base.util.DateUtils;
import com.ch.base.util.FileUtils;
import com.ch.base.util.StringUtils;
import com.ch.test.dao.CmsOfflineContentsWrapper;

public class GenerateCodeAction extends BaseAction {
	private final Logger log = Logger.getLogger(this.getClass());

	private List allTableList;
	private List tableDataList;
	private String dbType;
	private String tableName;
	private String packageName;
	private String formatUpperTableName;
	private String formatLowerTableName;
	private ArrayList pkList;

	private InputStream inputStream;
	private String daoClassName;
	private String wrapperClassName;

	public String execute() throws Exception{
		Connection con = null;
		try {
			con = this.getDBPoolConnection();
			GenerateCodeDAO GenerateCodeDAO = new GenerateCodeDAO(con);
			allTableList = GenerateCodeDAO.queryAllTable();
		} catch (Exception e) {
			e.printStackTrace();
			con.rollback();
			errorMsg = e.toString();
			return "exception";
		} finally {
			con.close();
		}
		return SUCCESS;
	}

	public String generateWrapperCode() throws Exception{
		Connection con = null;
		try {
			con = this.getDBPoolConnection();
			GenerateCodeDAO GenerateCodeDAO = new GenerateCodeDAO(con);
			tableDataList = GenerateCodeDAO.query(tableName);
			formatUpperTableName = StringUtils.proper(tableName, "_");

			wrapperClassName = formatUpperTableName + "Wrapper";
			StringBuffer sbStr = new StringBuffer();
			sbStr.append("package " + packageName + ";\n");
			sbStr.append("\n");
			sbStr.append("import com.ch.base.struts2.base.BaseWrapper;\n");
			sbStr.append("\n");
			sbStr.append("/**\n");
			sbStr.append(" * Class Name : " + wrapperClassName +"\n");
			sbStr.append(" * Description: \n");
			sbStr.append(" * Copyright  : Copyright (c) " + DateUtils.getNowDateTime("AD").getYear()+ "\n");
			//			sbStr.append(" * Company    : ARES\n");
			sbStr.append(" * @version 1.0\n");
			sbStr.append(" */\n");
			sbStr.append("public class " + formatUpperTableName + "Wrapper extends BaseWrapper {\n");

			for(int i = 0; i<tableDataList.size(); i++){
				GenerateCodeWrapper GenerateCodeWrapper = (GenerateCodeWrapper) tableDataList.get(i);
				sbStr.append("	private String " + GenerateCodeWrapper.getFormatLowerColumnName() + ";	");
				if (StringUtils.isNotEmpty(GenerateCodeWrapper.getComments())) {
					sbStr.append("//" + GenerateCodeWrapper.getComments() + "\n");
				} else {
					sbStr.append("\n");
				}
			}

			sbStr.append("\n");

			for(int i = 0; i<tableDataList.size(); i++){
				GenerateCodeWrapper GenerateCodeWrapper = (GenerateCodeWrapper) tableDataList.get(i);
				sbStr.append("	public String get" + GenerateCodeWrapper.getFormatUpperColumnName() + "() {\n");
				sbStr.append("		return " + GenerateCodeWrapper.getFormatLowerColumnName() + ";\n");
				sbStr.append("	}\n");
				sbStr.append("	public void set" + GenerateCodeWrapper.getFormatUpperColumnName() + "(String " + GenerateCodeWrapper.getFormatLowerColumnName() + ") {\n");
				sbStr.append("		this." + GenerateCodeWrapper.getFormatLowerColumnName() + " = " + GenerateCodeWrapper.getFormatLowerColumnName()+ ";\n");
				sbStr.append("	}\n");
			}

			sbStr.append("}");

			FileUtils.writeTextByUtf8(sbStr.toString(), "/generatCode/", wrapperClassName+".java");
			inputStream = ServletActionContext.getServletContext().getResourceAsStream("/generatCode/" + wrapperClassName+".java");
			//檔案不存在
			if(inputStream == null){
				errorMsg = "很抱歉，您所下載的檔案可能已被移除。";
				return "exception";
			}
		} catch (Exception e) {
			e.printStackTrace();
			con.rollback();
			errorMsg = e.toString();
			return "exception";
		} finally {
			con.close();
		}
		return "generateWrapperCode";
	}

	public String generateDAOCode() throws Exception{
		Connection con = null;
		try {
			con = this.getDBPoolConnection();
			GenerateCodeDAO GenerateCodeDAO = new GenerateCodeDAO(con);
			tableDataList = GenerateCodeDAO.query(tableName);
			formatUpperTableName = StringUtils.proper(tableName, "_");
			pkList = new ArrayList();
			for(int i = 0; i<tableDataList.size(); i++){
				GenerateCodeWrapper GenerateCodeWrapper = (GenerateCodeWrapper) tableDataList.get(i);
				if(GenerateCodeWrapper.getIsPrimaryKey().equals("Y")){
					String[] pkArray = new String[3];
					pkArray[0] = GenerateCodeWrapper.getColumnName();
					pkArray[1] = GenerateCodeWrapper.getFormatLowerColumnName();
					pkArray[2] = GenerateCodeWrapper.getComments() == null ? "" : GenerateCodeWrapper.getComments();
					pkList.add(pkArray);
				}
			}

			daoClassName = formatUpperTableName + "DAO";
			wrapperClassName = formatUpperTableName + "Wrapper";

			StringBuffer sbStr = new StringBuffer();
			sbStr.append("package " + packageName + ";\n");
			sbStr.append("\n");
			sbStr.append("import java.sql.Connection;\n");
			//			sbStr.append("import java.sql.ResultSet;\n");
			//			sbStr.append("import java.sql.ResultSetMetaData;\n");
			//			sbStr.append("import java.sql.SQLException;\n");
			sbStr.append("import java.sql.Timestamp;\n");
			sbStr.append("import java.util.ArrayList;\n");
			sbStr.append("import java.util.List;\n");
			sbStr.append("import java.util.Map;\n");
			sbStr.append("\n");
			//			sbStr.append("import com.ch.base.db.DBBean;\n");
			//			sbStr.append("import com.ch.base.util.StringUtils;\n");
			//			sbStr.append("import com.ch.base.obj.QueryResultObj;\n");
			//			sbStr.append("import com.ch.base.obj.QuerySettingObj;\n");
			sbStr.append("import com.taiwanmobile.mhealth.base.element.DBBean;\n");
			sbStr.append("import com.taiwanmobile.mhealth.base.util.StringUtils;\n");
			sbStr.append("import com.taiwanmobile.mhealth.base.obj.QueryResultObj;\n");
			sbStr.append("import com.taiwanmobile.mhealth.base.obj.QuerySettingObj;\n");
			sbStr.append("import com.taiwanmobile.mhealth.wrapper." + wrapperClassName + ";\n");

			sbStr.append("\n");

			sbStr.append("/**\n");
			sbStr.append(" * Class Name : " + daoClassName +"\n");
			sbStr.append(" * Description: \n");
			sbStr.append(" * Copyright  : Copyright (c) " + DateUtils.getNowDateTime("AD").getYear()+ "\n");
			//			sbStr.append(" * Company    : ARES\n");
			sbStr.append(" * @version 1.0\n");
			sbStr.append(" */\n");
			sbStr.append("public class " + daoClassName + " extends DBBean {\n");
			sbStr.append("\n");
			//			sbStr.append("	private String returnType = \"\";\n");
			//			sbStr.append("\n");

			sbStr.append("	public " + daoClassName + "(Connection con){\n");
			sbStr.append("		super(con);\n");
			sbStr.append("		this.setLogger(this.getClass().getName());\n");
			sbStr.append("	}\n");
			sbStr.append("\n");

			//			sbStr.append("	@Override\n");
			//			sbStr.append("	protected BaseWrapper populate(ResultSet rs) throws Exception {	\n");
			//			sbStr.append("		" + wrapperClassName + " wra = new " + wrapperClassName + "();\n");
			//			sbStr.append("		if (returnType.equals(\"dynamicQuery\")) {\n");
			//			sbStr.append("			//轉換為MetaData取出動態 Columns\n");
			//			sbStr.append("			ResultSetMetaData rsMD = rs.getMetaData();\n");
			//			sbStr.append("			String strValue;\n");
			//			sbStr.append("			for(int i = 1; i <= rsMD.getColumnCount(); i++) {\n");
			//			sbStr.append("				//取得欄位名稱\n");
			//			sbStr.append("				//wra.setFieldName(rsMD.getColumnName(i)==null ? \"\" : rsMD.getColumnName(i));\n");
			//			sbStr.append("				//取得欄位值 (註：透過getFieldValue(int)取值時 由 0 開始)\n");
			//			sbStr.append("				strValue = rs.getString(i);\n");
			//			sbStr.append("				wra.setFieldValue(strValue != null ? strValue : \"\" );\n");
			//			sbStr.append("			}\n");
			//			sbStr.append("		} else {\n");
			//			for(int i = 0; i<tableDataList.size(); i++){
			//				GenerateCodeWrapper GenerateCodeWrapper = (GenerateCodeWrapper) tableDataList.get(i);
			//				sbStr.append("			wra.set" + GenerateCodeWrapper.getFormatUpperColumnName()+ "(rs.getString(\"" + GenerateCodeWrapper.getColumnName() + "\") != null ? rs.getString(\"" + GenerateCodeWrapper.getColumnName() + "\") : \"\");\n");
			//			}
			//			sbStr.append("		}\n");
			//			sbStr.append("		return wra;\n");
			//			sbStr.append("	}\n");
			//			sbStr.append("\n");

			sbStr.append("	/**\n");
			sbStr.append("	 * 新增 insert(" + wrapperClassName + " qra)：\n");
			sbStr.append("	 * 	(1)	此method 回傳之型態為int，其值大於0表新增成功\n");
			sbStr.append("	 * @param " + wrapperClassName + "\n");
			sbStr.append("	 * @return\n");
			sbStr.append("	 * @throws Exception \n");
			sbStr.append("	 */\n");
			sbStr.append("	public int insert(" + wrapperClassName + " wra) throws Exception{\n");
			//			sbStr.append("		returnType = \"\";\n");
			sbStr.append("		int result = -1;\n");
			sbStr.append("		StringBuffer sbSql = new StringBuffer();\n");
			sbStr.append("		sbSql.append(\"insert into " + tableName+ " ( \");\n"); 
			for(int i = 0; i<tableDataList.size(); i++){
				GenerateCodeWrapper GenerateCodeWrapper = (GenerateCodeWrapper) tableDataList.get(i);
				if(i < tableDataList.size()-1){
					sbStr.append("		sbSql.append(\" " + GenerateCodeWrapper.getColumnName() + ", \");\n");
				}else{
					sbStr.append("		sbSql.append(\" " + GenerateCodeWrapper.getColumnName() + ") \");\n");
				}
			}
			sbStr.append("		sbSql.append(\"values (");
			for(int i = 0; i<tableDataList.size(); i++){
				GenerateCodeWrapper GenerateCodeWrapper = (GenerateCodeWrapper) tableDataList.get(i);
				if(i < tableDataList.size()-1){
					sbStr.append("?, ");
				}else{
					sbStr.append("?);\");\n");
				}
			}
			sbStr.append("\n");
			sbStr.append("		int count = 0;\n");
			sbStr.append("		Object[] params = new Object[" + tableDataList.size() + "];\n");
			for(int i = 0; i<tableDataList.size(); i++){
				GenerateCodeWrapper GenerateCodeWrapper = (GenerateCodeWrapper) tableDataList.get(i);
				if(!GenerateCodeWrapper.getDataType().equals("DATE")){
					sbStr.append("		params[count++] = wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "();\n");
				}else{
					sbStr.append("		params[count++] = StringUtils.isNotEmpty(wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "()) ? Timestamp.valueOf(wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "()) : null;\n");
				}
			}
			sbStr.append("\n");
			sbStr.append("		try {\n");
			sbStr.append("			log.info(\"由 \" + Thread.currentThread().getStackTrace()[2] + \" 呼叫執行 \" + Thread.currentThread().getStackTrace()[1].getMethodName() + \": \" + this.getPreparedSQL(sbSql.toString(), params));\n");
			sbStr.append("			result = this.executeUpdate(sbSql.toString(), params);\n");
			sbStr.append("		} catch (Exception e) {\n");
			sbStr.append("			log.error(\"由 \" + Thread.currentThread().getStackTrace()[2] + \" 呼叫執行 \" + Thread.currentThread().getStackTrace()[1].getMethodName() + \": \" + this.getPreparedSQL(sbSql.toString(), params));\n");
			sbStr.append("			throw e;\n");
			sbStr.append("		}\n");
			sbStr.append("\n");
			sbStr.append("		return result;	\n");
			sbStr.append("	}\n");
			sbStr.append("\n");

			if (pkList != null && pkList.size() > 0) {
				sbStr.append("	/**\n");
				sbStr.append("	 * 修改 updateByPK(" + wrapperClassName + " wra, ");
				for(int i=0; i<pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					if(i < pkList.size()-1){
						sbStr.append("String " + pkArray[1] + ", ");
					}else{
						sbStr.append("String " + pkArray[1] + ")：\n");
					}
				}
				sbStr.append("	 * 	(1)	此method 回傳之型態為int，其值大於0表修改成功\n");
				sbStr.append("	 * @param " + wrapperClassName + "\n");
				for(int i=0; i<pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					sbStr.append("	 * @param " + pkArray[1] + "	" + pkArray[2] + "\n");
				}

				sbStr.append("	 * @return\n");
				sbStr.append("	 * @throws Exception \n");
				sbStr.append("	 */\n");
				sbStr.append("	public int updateByPK(" + wrapperClassName + " wra, ");
				for(int i=0; i<pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					if(i < pkList.size()-1){
						sbStr.append("String " + pkArray[1] + ", ");
					}else{
						sbStr.append("String " + pkArray[1] + ") throws Exception{\n");
					}
				}
				//			sbStr.append("		returnType = \"\";\n");
				sbStr.append("		int result = -1 ;\n");
				sbStr.append("		StringBuffer sbSql = new StringBuffer();\n");
				sbStr.append("		ArrayList al = new ArrayList();\n");
				sbStr.append("		sbSql.append(\"update " + tableName + " set \");\n");
				for(int i = 0; i<tableDataList.size(); i++){
					GenerateCodeWrapper GenerateCodeWrapper = (GenerateCodeWrapper) tableDataList.get(i);
					if(!GenerateCodeWrapper.getDataType().equals("DATE")){
						sbStr.append("		if( wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "() != null ) { sbSql.append(\" " + GenerateCodeWrapper.getColumnName() + " = ?, \"); al.add( wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "() ); }\n");
					}else{
						sbStr.append("		if( wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "() != null ) { sbSql.append(\" " + GenerateCodeWrapper.getColumnName() + " = ?, \"); al.add( Timestamp.valueOf(wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "()) ); }\n");
					}
				}			
				sbStr.append("\n");
				sbStr.append("		sbSql.delete(sbSql.toString().lastIndexOf(\",\"), sbSql.toString().length());\n");
				sbStr.append("\n");
				sbStr.append("		sbSql.append(\" where 1 = 1 \");\n");
				for(int i=0; i<pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					sbStr.append("		sbSql.append(\"   and " + pkArray[0] + " = ? \");\n");
					sbStr.append("		al.add(" + pkArray[1] + "); \n");
				}
				sbStr.append("\n");
				sbStr.append("		Object[] params = al.toArray();\n");
				sbStr.append("\n");
				sbStr.append("		try{\n");
				sbStr.append("			log.info(\"由 \" + Thread.currentThread().getStackTrace()[2] + \" 呼叫執行 \" + Thread.currentThread().getStackTrace()[1].getMethodName() + \": \" + this.getPreparedSQL(sbSql.toString(), params));\n");
				sbStr.append("			result = this.executeUpdate(sbSql.toString(), params);\n");
				sbStr.append("		} catch (Exception e) {\n");
				sbStr.append("			log.error(\"由 \" + Thread.currentThread().getStackTrace()[2] + \" 呼叫執行 \" + Thread.currentThread().getStackTrace()[1].getMethodName() + \": \" + this.getPreparedSQL(sbSql.toString(), params));\n");
				sbStr.append("			throw e;\n");
				sbStr.append("		}\n");
				sbStr.append("\n");
				sbStr.append("		return result;\n");
				sbStr.append("	}\n");
				sbStr.append("\n");
			}

			sbStr.append("	/**\n");
			sbStr.append("	 * 修改 update(" + wrapperClassName + " wra, String whereClause, Object[] whereArgs)：\n");
			sbStr.append("	 * 	(1)	此method 回傳之型態為int，其值大於0表修改成功\n");
			sbStr.append("	 * 	(2)	whereClause 為修改時的限定條件，例如日期區間...等等，由呼叫者組好傳入，前面需含 \" and \" 做為啟始字串。\n");
			sbStr.append("	 * @param " + wrapperClassName + "\n");
			sbStr.append("	 * @param whereClause\n");
			sbStr.append("	 * @param whereArgs\n");
			sbStr.append("	 * @return\n");
			sbStr.append("	 * @throws Exception \n");
			sbStr.append("	 */\n");
			sbStr.append("	public int update(" + wrapperClassName + " wra, String whereClause, Object[] whereArgs)throws Exception{\n");
			//			sbStr.append("		returnType = \"\";\n");
			sbStr.append("		int result = -1 ;\n");
			sbStr.append("		StringBuffer sbSql = new StringBuffer();\n");
			sbStr.append("		ArrayList al = new ArrayList();\n");
			sbStr.append("		sbSql.append(\"update " + tableName + " set \");\n");
			for(int i = 0; i<tableDataList.size(); i++){
				GenerateCodeWrapper GenerateCodeWrapper = (GenerateCodeWrapper) tableDataList.get(i);
				if(!GenerateCodeWrapper.getDataType().equals("DATE")){
					sbStr.append("		if( wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "() != null ) { sbSql.append(\" " + GenerateCodeWrapper.getColumnName() + " = ?, \"); al.add( wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "() ); }\n");
				}else{
					sbStr.append("		if( wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "() != null ) { sbSql.append(\" " + GenerateCodeWrapper.getColumnName() +" = ?, \"); al.add( Timestamp.valueOf(wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "()) ); }\n");
				}
			}
			sbStr.append("\n");
			sbStr.append("		sbSql.delete(sbSql.toString().lastIndexOf(\",\"), sbSql.toString().length());\n");
			sbStr.append("\n");
			sbStr.append("		sbSql.append(\" where 1 = 1 \");\n");
			sbStr.append("		sbSql.append(whereClause);\n");
			sbStr.append("		if(whereArgs != null && whereArgs.length > 0){\n");
			sbStr.append("			for(int i = 0; i < whereArgs.length; i++){\n");
			sbStr.append("				al.add( whereArgs[i] );\n");
			sbStr.append("			}\n");
			sbStr.append("		}\n");
			sbStr.append("\n");
			sbStr.append("		Object[] params = al.toArray();\n");
			sbStr.append("\n");
			sbStr.append("		try{\n");
			sbStr.append("			if(StringUtils.isEmpty(whereClause)){\n");
			sbStr.append("				throw new Exception(\"因未輸入 where 條件式，避免異動到所有資料，故強制關閉此update指令。\");\n");
			sbStr.append("			}\n");
			sbStr.append("			log.info(\"update(): \" + this.getPreparedSQL(sbSql.toString(), params));\n");
			sbStr.append("			result = this.executeUpdate(sbSql.toString(), params);\n");
			sbStr.append("		} catch (Exception e) {\n");
			sbStr.append("			log.error(\"SQL in \" + this.getClass().getSimpleName() + \" update(): \" + this.getPreparedSQL(sbSql.toString(), params) + \" executed failed.\");\n");
			sbStr.append("			throw e;\n");
			sbStr.append("		}\n");
			sbStr.append("\n");
			sbStr.append("		return result;\n");
			sbStr.append("	}\n");
			sbStr.append("\n");

			if (pkList != null && pkList.size() > 0) {
				sbStr.append("	/**\n");
				sbStr.append("	 * 刪除 deleteByPK(");
				for(int i=0; i<pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					if(i < pkList.size()-1){
						sbStr.append("String " + pkArray[1] + ", ");
					}else{
						sbStr.append("String " + pkArray[1] + ")：\n");
					}
				}
				sbStr.append("	 * 	(1)	此method 回傳之型態為int，其值大於0表刪除成功。\n");
				for(int i=0; i<pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					sbStr.append("	 * @param " + pkArray[1] + "	" + pkArray[2] + "\n");
				}			
				sbStr.append("	 * @return\n");
				sbStr.append("	 * @throws Exception\n");
				sbStr.append("	 */\n");
				sbStr.append("	public int deleteByPK(");
				for(int i=0; i<pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					if(i < pkList.size()-1){
						sbStr.append("String " + pkArray[1] + ", ");
					}else{
						sbStr.append("String " + pkArray[1] + ") throws Exception{\n");
					}
				}
				//			sbStr.append("		returnType = \"\";\n");
				sbStr.append("		int result = -1;\n");
				sbStr.append("		StringBuffer sbSql = new StringBuffer();\n");
				sbStr.append("		sbSql.append(\"delete from " + tableName + " \"); \n");
				sbStr.append("		sbSql.append(\" where 1 = 1 \");\n");
				for(int i=0; i<pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					sbStr.append("		sbSql.append(\"   and " + pkArray[0] + " = ? \");\n");
				}
				sbStr.append("\n");
				sbStr.append("		int count = 0;\n");
				sbStr.append("		Object[] params = new Object[" + pkList.size() + "];\n");
				for(int i=0; i<pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					sbStr.append("		params[count++] = " + pkArray[1] + ";\n");
				}
				sbStr.append("\n");
				sbStr.append("		try {\n");
				sbStr.append("			log.info(\"由 \" + Thread.currentThread().getStackTrace()[2] + \" 呼叫執行 \" + Thread.currentThread().getStackTrace()[1].getMethodName() + \": \" + this.getPreparedSQL(sbSql.toString(), params));\n");
				sbStr.append("			result = this.executeUpdate(sbSql.toString(), params);\n");
				sbStr.append("		} catch (Exception e) {\n");
				sbStr.append("			log.error(\"由 \" + Thread.currentThread().getStackTrace()[2] + \" 呼叫執行 \" + Thread.currentThread().getStackTrace()[1].getMethodName() + \": \" + this.getPreparedSQL(sbSql.toString(), params));\n");
				sbStr.append("			throw e;\n");
				sbStr.append("		}\n");
				sbStr.append("\n");
				sbStr.append("		return result;\n");
				sbStr.append("	}\n");
				sbStr.append("\n");
			}

			sbStr.append("	/**\n");
			sbStr.append("	 * 刪除 delete(" + wrapperClassName + " wra, String whereClause, Object[] whereArgs)：\n");
			sbStr.append("	 * 	(1)	此method 回傳之型態為int，其值大於0表刪除成功。\n");
			sbStr.append("	 * 	(2)	whereClause 為不在 wrapper 傳入欄位的限制條件，例如日期區間...等等，由呼叫者組好傳入，前面需含 \" and \" 做為啟始字串。\n");
			sbStr.append("	 * @param " + wrapperClassName + "\n");
			sbStr.append("	 * @param whereClause\n");
			sbStr.append("	 * @param whereArgs\n");
			sbStr.append("	 * @return\n");
			sbStr.append("	 * @throws Exception\n");
			sbStr.append("	 */\n");
			sbStr.append("	public int delete(" + wrapperClassName + " wra, String whereClause, Object[] whereArgs) throws Exception{\n");
			//			sbStr.append("		returnType = \"\";\n");
			sbStr.append("		int result = -1;\n");
			sbStr.append("		StringBuffer sbSql = new StringBuffer();\n");
			sbStr.append("		ArrayList al = new ArrayList();\n");
			sbStr.append("		sbSql.append(\"delete " + tableName + " \");\n");
			sbStr.append("		sbSql.append(\" where 1 = 1 \");\n");
			sbStr.append("\n");
			sbStr.append("		if (wra != null) {\n");
			for(int i = 0; i<tableDataList.size(); i++){
				GenerateCodeWrapper GenerateCodeWrapper = (GenerateCodeWrapper) tableDataList.get(i);
				if(!GenerateCodeWrapper.getDataType().equals("DATE")){
					sbStr.append("			if(StringUtils.isNoNull(wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "())) { sbSql.append(\" and " + GenerateCodeWrapper.getColumnName() + " like ? \"); al.add( wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "() ); }\n");
				}else{
					sbStr.append("			if(StringUtils.isNoNull(wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "())) { sbSql.append(\" and " + GenerateCodeWrapper.getColumnName() + " like ? \"); al.add( Timestamp.valueOf(wra.get" + GenerateCodeWrapper.getFormatUpperColumnName() + "()) ); }\n");
				}
			}
			sbStr.append("		}\n");
			sbStr.append("\n");		
			sbStr.append("		sbSql.append(whereClause);\n");
			sbStr.append("		if(whereArgs != null && whereArgs.length > 0){\n");
			sbStr.append("			for(int i = 0; i < whereArgs.length; i++){\n");
			sbStr.append("				al.add( whereArgs[i] );\n");
			sbStr.append("			}\n");
			sbStr.append("		}\n");
			sbStr.append("\n");
			sbStr.append("		Object[] params = al.toArray();\n");
			sbStr.append("\n");
			sbStr.append("		try {\n");
			sbStr.append("			if(wra == null && StringUtils.isEmpty(whereClause)){\n");
			sbStr.append("				throw new Exception(\"因未輸入 where 條件式，避免異動到所有資料，故強制關閉此delete指令。\");\n");
			sbStr.append("			}\n");
			sbStr.append("			log.info(\"由 \" + Thread.currentThread().getStackTrace()[2] + \" 呼叫執行 \" + Thread.currentThread().getStackTrace()[1].getMethodName() + \": \" + this.getPreparedSQL(sbSql.toString(), params));\n");
			sbStr.append("			result = this.executeUpdate(sbSql.toString(), params);\n");
			sbStr.append("		} catch (Exception e) {\n");
			sbStr.append("			log.error(\"由 \" + Thread.currentThread().getStackTrace()[2] + \" 呼叫執行 \" + Thread.currentThread().getStackTrace()[1].getMethodName() + \": \" + this.getPreparedSQL(sbSql.toString(), params));\n");

			sbStr.append("			throw e;\n");
			sbStr.append("		}\n");
			sbStr.append("\n");
			sbStr.append("		return result;	\n");
			sbStr.append("	}\n");
			sbStr.append("\n");

			if (pkList != null && pkList.size() > 0) {
				sbStr.append("	/**\n");
				sbStr.append("	 * 查詢 queryByPK(");
				for(int i=0; i<pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					if(i < pkList.size()-1){
						sbStr.append("String " + pkArray[1] + ", ");
					}else{
						sbStr.append("String " + pkArray[1] + ")：\n");
					}
				}
				sbStr.append("	 * 	(1)	此method 回傳之型態為單一 wrapper。\n");
				for(int i=0; i<pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					sbStr.append("	 * @param " + pkArray[1] + "	" + pkArray[2] + "\n");
				}
				sbStr.append("	 * @return " + wrapperClassName + "\n");
				sbStr.append("	 * @throws Exception\n");
				sbStr.append("	 */\n");
				sbStr.append("	public " + wrapperClassName + " queryByPK(");
				for(int i=0; i<pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					if(i < pkList.size()-1){
						sbStr.append("String " + pkArray[1] + ", ");
					}else{
						sbStr.append("String " + pkArray[1] + ") throws Exception{\n");
					}
				}
				//			sbStr.append("		returnType = \"\";\n");
				sbStr.append("		" + wrapperClassName + " result = new " + wrapperClassName + "();\n");
				sbStr.append("		StringBuffer sbSql = new StringBuffer();\n");
				sbStr.append("		sbSql.append(\"select * \"); \n");
				sbStr.append("		sbSql.append(\"  from " + tableName + " \");\n");
				sbStr.append("		sbSql.append(\" where 1 = 1 \");\n");
				for(int i=0; i<pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					sbStr.append("		sbSql.append(\"   and " + pkArray[0] + " = ? \");\n");
				}
				sbStr.append("\n");
				sbStr.append("		int count = 0;\n");
				sbStr.append("		Object[] params = new Object[" + pkList.size() + "];\n");
				for(int i=0; i<pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					sbStr.append("		params[count++] = " + pkArray[1] + ";\n");
				}
				sbStr.append("\n");
				sbStr.append("		try {\n");
				sbStr.append("			log.info(\"由 \" + Thread.currentThread().getStackTrace()[2] + \" 呼叫執行 \" + Thread.currentThread().getStackTrace()[1].getMethodName() + \": \" + this.getPreparedSQL(sbSql.toString(), params));\n");
				//				sbStr.append("			List<" + wrapperClassName + "> list = this.executeQuery(" + wrapperClassName + ".class, sbSql.toString(), params);\n");
				sbStr.append("			List<" + wrapperClassName + "> list = ((QueryResultObj) this.executeQuery(" + wrapperClassName + ".class, sbSql.toString(), params)).getQueryList();\n");
				sbStr.append("\n");
				sbStr.append("			if( ( list != null ) && ( list.size() > 0 ) ){\n");
				sbStr.append("				result = (" + wrapperClassName + ") list.get(0);\n");
				sbStr.append("			}\n");
				sbStr.append("		} catch (Exception e) {\n");
				sbStr.append("			log.error(\"由 \" + Thread.currentThread().getStackTrace()[2] + \" 呼叫執行 \" + Thread.currentThread().getStackTrace()[1].getMethodName() + \": \" + this.getPreparedSQL(sbSql.toString(), params));\n");
				sbStr.append("			throw e;\n");
				sbStr.append("		}\n");
				sbStr.append("\n");
				sbStr.append("		return result;\n");
				sbStr.append("	}\n");
				sbStr.append("\n");
			}

			sbStr.append("	/**\n");
			//			sbStr.append("	 * 查詢 query(" + wrapperClassName + " wra, String whereClause, Object[] whereArgs, String other)：\n");
			sbStr.append("	 * 查詢 query(String[] columns, " + wrapperClassName + " wra, String whereClause, String[] whereArgs, String groupBy, String having, String orderBy, String limit, String offset)：\n");
			sbStr.append("	 * 	(1)	此method 回傳之型態為 QueryResultObj\n");
			sbStr.append("	 * 	(2)	whereClause 為不在 wrapper 傳入欄位的查詢條件，例如日期區間...等等，由呼叫者組好傳入，前面需含 \" and \" 做為啟始字串。\n");
			//			sbStr.append("	 * 	(3)	other 字串可為 \"order by...\" 或 \"group by...\"，當字串不為 null 或 空字串時，取代整段預設 order by / group by 字串。\n");
			sbStr.append("	 * @param columns\n");
			sbStr.append("	 * @param wra\n");
			sbStr.append("	 * @param whereClause\n");
			sbStr.append("	 * @param whereArgs\n");
			//			sbStr.append("	 * @param other\n");
			sbStr.append("	 * @param groupBy\n");
			sbStr.append("	 * @param having\n");
			sbStr.append("	 * @param orderBy\n");
			sbStr.append("	 * @param limit\n");
			sbStr.append("	 * @param offset\n");
			//			sbStr.append("	 * @return List<" + wrapperClassName + ">\n");
			sbStr.append("	 * @return QueryResultObj 物件\n");
			sbStr.append("	 * @throws Exception\n");
			sbStr.append("	 */\n");
			//			sbStr.append("	public List<" + wrapperClassName + "> query(" + wrapperClassName + " wra, String whereClause, Object[] whereArgs, String other) throws Exception{\n");
			//			sbStr.append("	public List<" + wrapperClassName + "> query(String[] columns, " + wrapperClassName + " wra, String whereClause, String[] whereArgs, String groupBy, String having, String orderBy, String limit, String offset) throws Exception{\n");
			sbStr.append("	public QueryResultObj query(String[] columns, " + wrapperClassName + " wra, String whereClause, String[] whereArgs, String groupBy, String having, String orderBy, String limit, String offset) throws Exception{\n");
			//			sbStr.append("		returnType = \"\";\n");
			//			sbStr.append("		List<" + wrapperClassName + "> result = new ArrayList<" + wrapperClassName + ">();\n");
			sbStr.append("		QueryResultObj queryResultObj = new QueryResultObj();\n");
			sbStr.append("		StringBuffer sbSql = new StringBuffer();\n");
			sbStr.append("		ArrayList al = new ArrayList();\n");
			sbStr.append("		sbSql.append(\"select \"); \n");
			sbStr.append("		if (columns != null && columns.length > 0) {\n");
			sbStr.append("			for (int i = 0; i < columns.length; i++) {\n");
			sbStr.append("				if (i < columns.length - 1) {\n");
			sbStr.append("					sbSql.append( columns[i] + \", \");\n");
			sbStr.append("				}else{\n");
			sbStr.append("					sbSql.append( columns[i] );\n");
			sbStr.append("				}\n");
			sbStr.append("			}\n");
			sbStr.append("		} else {\n");
			sbStr.append("			sbSql.append(\" * \"); \n");
			sbStr.append("		}\n");
			sbStr.append("		sbSql.append(\"  from " + tableName + " \");\n");
			sbStr.append("		sbSql.append(\" where 1 = 1 \");\n");
			sbStr.append("\n");
			sbStr.append("		if (wra != null) {\n");
			for(int i = 0; i<tableDataList.size(); i++){
				GenerateCodeWrapper generateCodeWrapper = (GenerateCodeWrapper) tableDataList.get(i);
				if(!generateCodeWrapper.getDataType().equals("DATE")){
					sbStr.append("			if(StringUtils.isNotNull(wra.get" + generateCodeWrapper.getFormatUpperColumnName() + "()))   { sbSql.append(\" and " + generateCodeWrapper.getColumnName() + " like ? \"); al.add( wra.get" + generateCodeWrapper.getFormatUpperColumnName() + "() ); }\n");
				}else{
					sbStr.append("			if(StringUtils.isNotNull(wra.get" + generateCodeWrapper.getFormatUpperColumnName() + "()))   { sbSql.append(\" and " + generateCodeWrapper.getColumnName() + " like ? \"); al.add( Timestamp.valueOf(wra.get" + generateCodeWrapper.getFormatUpperColumnName() + "()) ); }\n");
				}
			}
			sbStr.append("		}\n");
			sbStr.append("\n");
			sbStr.append("		if (StringUtils.isNotEmpty(whereClause)) {\n");
			sbStr.append("			sbSql.append(whereClause);\n");
			sbStr.append("		}\n");
			sbStr.append("\n");
			sbStr.append("		if(whereArgs != null && whereArgs.length > 0){\n");
			sbStr.append("			for(int i=0; i < whereArgs.length; i++){\n");
			sbStr.append("				al.add( whereArgs[i] );\n");
			sbStr.append("			}\n");
			sbStr.append("		}\n");
			sbStr.append("\n");
			//			sbStr.append("		if(StringUtils.isEmpty(other)){\n");
			//			sbStr.append("			/*\n");
			//			sbStr.append("			 *	請填入預設之\"order by...\" 或 \"group by...\"\n");
			//			sbStr.append("			 */\n");
			//			if(pkList != null && pkList.size() > 0){
			//				sbStr.append("			sbSql.append(\" order by ");
			//				for(int i=0; i<pkList.size(); i++){
			//					String[] pkArray = (String[]) pkList.get(i);
			//					if(i<pkList.size()-1){
			//						sbStr.append(pkArray[0] + ", ");
			//					}else{
			//						sbStr.append(pkArray[0] + "\");\n");
			//					}
			//				}
			//			}
			//			sbStr.append("		}else{\n");
			//			sbStr.append("			sbSql.append(other);\n");
			//			sbStr.append("		}\n");
			//			sbStr.append("\n");

			if(pkList != null && pkList.size() > 0){
				sbStr.append("		if (StringUtils.isEmpty(orderBy)) {\n");
				sbStr.append("			/*\n");
				sbStr.append("			 *	若無傳入orderBy之數值, 則以預設值(PK值)取代\n");
				sbStr.append("			 */\n");
				sbStr.append("			orderBy = \"");
				for(int i = 0; i < pkList.size(); i++){
					String[] pkArray = (String[]) pkList.get(i);
					if(i < pkList.size() - 1){
						sbStr.append(pkArray[0] + ", ");
					}else{
						sbStr.append(pkArray[0] + "\";\n");
					}
				}
				sbStr.append("		}\n");
				sbStr.append("\n");
			}

			//			sbStr.append("		Object[] params = al.toArray();\n");
			sbStr.append("		QuerySettingObj querySettingObj = new QuerySettingObj();\n");
			sbStr.append("		querySettingObj.setSql(sbSql.toString());\n");
			sbStr.append("		querySettingObj.setPreparedStatementParams(al.toArray());\n");
			sbStr.append("		querySettingObj.setGroupBy(groupBy);\n");
			sbStr.append("		querySettingObj.setHaving(having);\n");
			sbStr.append("		querySettingObj.setOrderBy(orderBy);\n");
			sbStr.append("		querySettingObj.setLimit(limit);\n");
			sbStr.append("		querySettingObj.setOffset(offset);\n");
			sbStr.append("\n");
			sbStr.append("		try {\n");
			sbStr.append("			log.info(\"由 \" + Thread.currentThread().getStackTrace()[2] + \" 呼叫執行 \" + Thread.currentThread().getStackTrace()[1].getMethodName() + \": \" + this.getPreparedSQL(querySettingObj));\n");
			//			sbStr.append("			result = this.executeQuery(sbSql.toString(), params);\n");
			sbStr.append("			if (columns != null && columns.length > 0) {\n"); 
			//			sbStr.append("				result = this.executeQuery(Map.class, sbSql.toString(), params, groupBy, having, orderBy, limit, offset);\n");
			//			sbStr.append("				queryResultObj = this.executeQuery(Map.class, sbSql.toString(), params, groupBy, having, orderBy, limit, offset);\n");
			//			sbStr.append("				queryResultObj = this.executeQuery(Map.class, sbSql.toString(), params, groupBy, having, orderBy, limit, offset);\n");
			sbStr.append("				querySettingObj.setResultType(Map.class);\n");
			sbStr.append("			} else {\n");
			//			sbStr.append("				result = this.executeQuery(" + wrapperClassName + ".class, sbSql.toString(), params, groupBy, having, orderBy, limit, offset);\n");
			//			sbStr.append("				queryResultObj = this.executeQuery(" + wrapperClassName + ".class, sbSql.toString(), params, groupBy, having, orderBy, limit, offset);\n");
			sbStr.append("				querySettingObj.setResultType(" + wrapperClassName + ".class);\n");
			sbStr.append("			}\n");
			sbStr.append("			queryResultObj = this.executeQuery(querySettingObj);\n");
			sbStr.append("		} catch (Exception e) {\n");
			sbStr.append("			log.error(\"由 \" + Thread.currentThread().getStackTrace()[2] + \" 呼叫執行 \" + Thread.currentThread().getStackTrace()[1].getMethodName() + \": \" + this.getPreparedSQL(querySettingObj));\n");
			sbStr.append("			throw e;\n");
			sbStr.append("		}\n");
			sbStr.append("\n");
			//			sbStr.append("		return result;\n");
			sbStr.append("		return queryResultObj;\n");
			sbStr.append("	}\n");
			sbStr.append("}\n");
			sbStr.append("\n");

			FileUtils.writeTextByUtf8(sbStr.toString(), "/generatCode/", daoClassName+".java");
			inputStream = ServletActionContext.getServletContext().getResourceAsStream("/generatCode/" + daoClassName+".java");
			//檔案不存在
			if(inputStream == null){
				errorMsg = "很抱歉，您所下載的檔案可能已被移除。";
				return "exception";
			}
		} catch (Exception e) {
			e.printStackTrace();
			con.rollback();
			errorMsg = e.toString();
			return "exception";
		} finally {
			con.close();
		}
		return "generateDAOCode";
	}

	public String generateActionCode() throws Exception{
		Connection con = null;
		try {
			con = this.getDBPoolConnection();			
			GenerateCodeDAO generateCodeDAO = new GenerateCodeDAO(con);
			allTableList = generateCodeDAO.queryAllTable();
			tableDataList = generateCodeDAO.query(tableName);
			formatUpperTableName = StringUtils.proper(tableName, "_");
			formatLowerTableName = StringUtils.lowerfirst(formatUpperTableName);
			pkList = new ArrayList();
			if(tableDataList!=null && tableDataList.size()>0){
				for(int i = 0; i<tableDataList.size(); i++){
					GenerateCodeWrapper generateCodeWrapper = (GenerateCodeWrapper) tableDataList.get(i);
					if(generateCodeWrapper.getIsPrimaryKey().equals("Y")){
						pkList.add(generateCodeWrapper.getFormatLowerColumnName());
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			con.rollback();
			errorMsg = e.toString();
			return "exception";
		} finally {
			con.close();
		}
		return SUCCESS;
	}

	public List getAllTableList() {
		return allTableList;
	}

	public void setAllTableList(List allTableList) {
		this.allTableList = allTableList;
	}

	public List getTableDataList() {
		return tableDataList;
	}

	public void setTableDataList(List tableDataList) {
		this.tableDataList = tableDataList;
	}

	public String getDbType() {
		return dbType;
	}

	public void setDbType(String dbType) {
		this.dbType = dbType;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public String getPackageName() {
		return packageName;
	}

	public void setPackageName(String packageName) {
		this.packageName = packageName;
	}

	public String getFormatUpperTableName() {
		return formatUpperTableName;
	}

	public void setFormatUpperTableName(String formatUpperTableName) {
		this.formatUpperTableName = formatUpperTableName;
	}

	public String getFormatLowerTableName() {
		return formatLowerTableName;
	}

	public void setFormatLowerTableName(String formatLowerTableName) {
		this.formatLowerTableName = formatLowerTableName;
	}

	public ArrayList getPkList() {
		return pkList;
	}

	public void setPkList(ArrayList pkList) {
		this.pkList = pkList;
	}

	public InputStream getInputStream() {
		return inputStream;
	}

	public void setInputStream(InputStream inputStream) {
		this.inputStream = inputStream;
	}

	public String getDaoClassName() {
		return daoClassName;
	}

	public void setDaoClassName(String daoClassName) {
		this.daoClassName = daoClassName;
	}

	public String getWrapperClassName() {
		return wrapperClassName;
	}

	public void setWrapperClassName(String wrapperClassName) {
		this.wrapperClassName = wrapperClassName;
	}

	public Logger getLog() {
		return log;
	}

}
